
import sqlalchemy
from sqlalchemy import create_engine, DATE, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker, relationship

#创建链接                数据库类型+要使用的底层sql实现://账号:密码@ip地址/数据库名称                            echo=True会把创建时候的信息打印下来
engine = create_engine('mysql+pymysql://root:qQ1002324991.@1.117.160.157/learn_db', encoding='utf-8')

Base = declarative_base() #生成orm基类

class Student(Base):
    __tablename__ = 'student'
    id = Column(Integer, primary_key=True)
    name = Column(String(32), nullable=False)
    register_date = Column(DATE, nullable=False)

    def __repr__(self):
        return 'id:%s name:%s register_date:%s' % (self.id, self.name, self.register_date)

class StudyRecord(Base):
    __tablename__ = 'student_record'
    id = Column(Integer, primary_key=True)
    day = Column(Integer, nullable=False)
    status = Column(String(32), nullable=False)
    stu_id = Column(Integer, ForeignKey('student.id'))

    #建立关联关系，在这个表里 可以通过student字段查Student表里的字段
    student = relationship('Student', backref='my_study_record')

    def __repr__(self):
        return 'id:%s day:%s status:%s stu_id:%s' % (self.student.name, self.day, self.status, self.stu_id)

Base.metadata.create_all(engine) #创建表结构
Session_class = sessionmaker(engine)    #这两条死记住，先创建与数据库的会话session_class这是个类
session = Session_class()   #实例化这个类

# s1 = Student(name='刘奇', register_date='2021-10-21')
# s2 = Student(name='石星', register_date='2021-10-22')
# s3 = Student(name='强哥', register_date='2021-10-20')
# s4 = Student(name='王楠', register_date='2021-10-19')
#
# study_obj1 = StudyRecord(day=1, status='yes', stu_id=1)
# study_obj2 = StudyRecord(day=2, status='no', stu_id=1)
# study_obj3 = StudyRecord(day=3, status='yes', stu_id=1)
# study_obj4 = StudyRecord(day=2, status='yes', stu_id=2)
# session.add_all([s1,s2,s3,s4, study_obj1,study_obj2,study_obj3,study_obj4])
stu_obj = session.query(Student).filter(Student.name == '刘奇').first()
print(stu_obj.my_study_record)
# session.commit()
